const sql = require('mssql');
const baseFn = require('../../public/base.js');
const fs = require('fs'); // 图片路径
const path = require('path'); // 图片路径
let uploadDir = './images/';

// 存储图片
const storagePic = (file, ind) => {
    let fileName = file.name;
    let newFileName = Date.now() + '_' + ind + fileName;
    let readStream = fs.createReadStream(file.path);
    let writeStream = fs.createWriteStream(uploadDir + newFileName);
    readStream.pipe(writeStream);
    return 'http://www.rsimg.com/' + newFileName
}
// 获取拼接的公司，部门的名称
const getJoinName = async (val) => {
    let companyResult = await sql.query(`SELECT STUFF((SELECT '-'+name FROM GSGL WHERE id in(${val}) FOR XML PATH('')),1,1,'') AS company`);
    return companyResult.recordset[0].company;
}
// 获取所有请假已审批员工信息
const getApplyLeaveAgreeInfo = async (ctx, next) => {
    let getBody = ctx.request.body;
    let searchList = getBody.searchList;
    let selectSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY J.id) AS PX,J.id,D.company,D.job,D.part,D.post,J.type,J.days,J.startTime,J.sellingOffStatus,J.sellingOffPic,J.sellingOffReason,J.endTime,J.applyTime,J.provePic,D.username FROM YG_QJJL J LEFT JOIN YG_DA D ON J.userId = D.id   WHERE J.STATUS = 'T' `;
    let totalCountSql = `SELECT COUNT(J.id) AS TOTAL FROM YG_QJJL J LEFT JOIN YG_DA D ON J.id = D.id  WHERE J.STATUS = 'T' `;
    if (searchList.username) {
        selectSql += ` AND (D.username LIKE '%${searchList.username}%' OR D.phone LIKE '%${searchList.username}%') `;
        totalCountSql += ` AND (D.username LIKE '%${searchList.username}%' OR D.phone LIKE '%${searchList.username}%') `;
    }
    if (0 in searchList.part) {
        selectSql += ` AND D.job LIKE '%${searchList.part.join(',')}%' `;
        totalCountSql += ` AND D.job LIKE '%${searchList.part.join(',')}%' `;
    }
    if (searchList.idNum) {
        selectSql += ` AND D.idnum LIKE '%${searchList.idNum}%' `;
        totalCountSql += ` AND D.idnum LIKE '%${searchList.idNum}%' `;
    }
    if (searchList.entryTime) {
        selectSql += ` AND D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
        totalCountSql += ` AND D.entrytime >= '${searchList.entryTime[0]}' AND D.entrytime <= '${searchList.entryTime[1]}' `;
    }
    selectSql += `) AS T1 WHERE T1.PX BETWEEN ${(getBody.pageIndex - 1) * (getBody.pageSize) + 1} AND ${getBody.pageIndex * (getBody.pageSize)} `;
    let result = await sql.query(selectSql);
    let total = await sql.query(totalCountSql);
    for (let item of result.recordset) {
        item.companyName = await getJoinName(item.company);
        item.partName = await getJoinName(item.part);
        item.postName = await getJoinName(item.post);
        item.jobName = await getJoinName(item.job);
    };
    ctx.body = { result: result.recordset, total: total.recordset[0].TOTAL };
}
// 提交销假
const addSellingOff = async (ctx, next) => {
    const files = ctx.request.files.proveFile;
    const body = ctx.request.body;
    let sellingOff= JSON.parse(body.sellingOff);
    let provePic = storagePic(files, '')
    let updateSql = `UPDATE  YG_QJJL SET sellingOffTime = '${sellingOff.sellingOffTime}',sellingOffPic = '${provePic}',sellingOffReason = '${sellingOff.reason}',sellingOffStatus = 'T' WHERE id = ${body.id} `;
    let result = await sql.query(updateSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = {
            success: true
        }
    } else {
        ctx.body = {
            success: false
        }
    }
}


module.exports = {
    'POST /getApplyLeaveAgreeInfo': getApplyLeaveAgreeInfo,
    'POST /addSellingOff': addSellingOff



}